package com.hongdee.crm.insurance.service.impl;

import com.hongdee.common.dao.IHibernateDao;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.dao.IJdbcDao;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.carConfig.service.ITcrmCarConfService;
import com.hongdee.crm.insurance.dao.ITCrmCarInsuranceDao;
import com.hongdee.crm.insurance.entity.TCrmCarInsurance;
import com.hongdee.crm.insurance.service.ITCrmCarInsuranceService;
import com.hongdee.crm.task.service.ICarBaseCountService;
import com.hongdee.crm.task.service.ITCrmCarWorkorderTaskService;
import com.hongdee.crm.task.service.ITCrmTimeTaskService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 * 名  称:InsuranceServiceImpl
 * 类  型: class
 * 描  述:
 * 路  径:com.hongdee.crm.insurance.service.impl;
 * 作  者: 术洪超
 * 日  期:2017-02-15 14:22
 */

@Service
public class TCrmCarInsuranceServiceImpl implements ITCrmCarInsuranceService {

    private String tabName = "T_CRM_CAR_INSURANCE";
    @Autowired
    private ITCrmCarInsuranceDao iTCrmCarInsuranceDao;
    @Autowired
    private IImpHibernateDao iImpHibernateDao;
    @Autowired
    private IImpJdbcDao iImpJdbcDao;

    @Autowired
    private IJdbcDao jdbcDao;

    @Autowired
    private IHibernateDao iHibernateDao;

    @Autowired
    private ITcrmCarConfService confService;

    @Autowired
    private ICarBaseCountService iCarBaseCountService;

    @Autowired
    private ITCrmCarWorkorderTaskService itCrmCarWorkorderTaskService;

    @Autowired
    private ITCrmTimeTaskService itCrmTimeTaskService;


    /**
     * @param id 主键
     * @return Map
     * @throws Exception
     * @desc 续保明细
     * @author 徐钦勇
     */
    @Override
    public Map info(String id) throws Exception {
        if (StringUtils.isEmpty(id)) {
            throw new Exception("请选择记录");
        }
        String sql = "select * from " + tabName + " where id = ? ";
        return this.iTCrmCarInsuranceDao.info(sql, new Object[]{id});
    }

    /**
     * @param start 起始记录
     * @param limit 页面大小
     * @return Map
     * @throws Exception
     * @desc 通过 JDBCDao分页查询
     * @author 术洪超
     */

    @Override
    public Map page(Map data, String start, String limit, String uid) throws Exception {
        List<Object> params = new ArrayList();
        StringBuffer sql = new StringBuffer();


        sql.append(" select * from t_crm_car_insurance a where a.company_Id = ? ");
        params.add(TokenUtil.getCompanyId(uid));



        //车系
        Object objSeries = data.get("series");
        if (objSeries != null) {
            ArrayList strSeries = (ArrayList) objSeries;
            if (strSeries.size() > 0) {
                StringBuffer tempBuf = new StringBuffer();
                sql.append(" and ( ");
                for (Object str : strSeries) {
                    tempBuf.append(" locate(? car_series) OR");
                    params.add((String) str);
                }
                sql.append(tempBuf.substring(0, tempBuf.length() - 2).toString());
                sql.append(" ) ");
            }

        }

        //状态
        String strStatus = String.valueOf(data.get("status"));
        if (!strStatus.isEmpty()) {
            Calendar calendar = Calendar.getInstance();
            Long dateCurrent = System.currentTimeMillis();

            switch (strStatus) {
                //一个月内到期
                case "1": {
                    sql.append(" and ((a.contractstopinsurance between ? and ?) or (a.compulsory_end_date between ? and ?)) ");
                    calendar.setTime(new Date());
                    calendar.add(Calendar.MONTH, 1);

                    params.add(dateCurrent);
                    params.add(calendar.getTime().getTime());
                    params.add(dateCurrent);
                    System.out.println(dateCurrent);
                    params.add(calendar.getTime().getTime());
                    System.out.println(calendar.getTime().getTime());
                }
                break;
                //两个月内到期
                case "2": {
                    sql.append(" and ((a.contractstopinsurance between ? and ?) or (a.compulsory_end_date between ? and ?)) ");
                    calendar.setTime(new Date());
                    calendar.add(Calendar.MONTH, 2);
                    Long time2 = calendar.getTime().getTime();

                    params.add(dateCurrent);
                    params.add(time2);
                    params.add(dateCurrent);
                    params.add(time2);
                    //System.out.println(time2);
                }
                break;
                //三个月内到期
                case "3": {
                    sql.append(" and ((a.contractstopinsurance between ? and ?) or (a.compulsory_end_date between ? and ?)) ");

                    calendar.setTime(new Date());
                    calendar.add(Calendar.MONTH, 3);
                    Long time3 = calendar.getTime().getTime();

                    params.add(dateCurrent);
                    params.add(time3);
                    params.add(dateCurrent);
                    params.add(time3);
                    //System.out.println(time3);
                }
                break;
                //未到期
                case "4":
                    sql.append(" and (a.contractstopinsurance > ? or a.compulsory_end_date > ?) ");
                    params.add(dateCurrent);
                    params.add(dateCurrent);
                    break;
                //已过期
                case "5": {
                    sql.append(" and (a.contractstopinsurance < ? or a.compulsory_end_date < ?) ");
                    params.add(dateCurrent);
                    params.add(dateCurrent);
                }
                break;
            }
        }
        //类型
        String strType = (String) data.get("type");
        if (!strType.isEmpty()) {
            sql.append(" and  a.type = ? ");
            params.add(strType);
        }

        //续保起始
        Map mapTime = (HashMap) data.get("time");
        if (mapTime.size() > 0) {
            String startTime = String.valueOf(mapTime.get("startTime"));
            System.out.println(startTime);
            if (startTime.length() > 0 && !"null".equals(startTime)) {
                Long startTimeLong = DateUtils.parseTime(startTime + " 00:00:00", "yyyy-MM-dd HH:mm:ss");
                System.out.println(startTimeLong);
                sql.append(" and ( a.contractstartinsurance >= ? or a.compulsory_start_date >= ? ) ");
                params.add(startTimeLong);
                params.add(startTimeLong);
                // System.out.println(startTimeLong);
            }

            String stopTime = String.valueOf(mapTime.get("stopTime"));
            System.out.println(stopTime);
            if (stopTime.length() > 0 && !"null".equals(stopTime)) {
                Long stopTimeLong = DateUtils.parseTime(stopTime + " 24:00:00", "yyyy-MM-dd HH:mm:ss");
                sql.append(" and ( a.contractstartinsurance < ? or a.compulsory_start_date < ? ) ");
                params.add(stopTimeLong);
                params.add(stopTimeLong);
                // System.out.println(stopTimeLong);

            }

        }
        //筛选输入项
        Map mapSelect = (HashMap) data.get("select");
        if (!"".equals(StringUtil.formatEmpty(mapSelect.get("value")))) {
            switch ((String) mapSelect.get("key")) {
                //车牌号
                case "1": {
                    sql.append(" and a.plate_num like ? ");
                    params.add("%" + mapSelect.get("value") + "%");
                }
                break;
                //车架号
                case "2": {
                    sql.append(" and a.vin like ? ");
                    params.add("%" + mapSelect.get("value") + "%");
                }
                break;
                //联系人姓名
                case "3": {
                    sql.append(" and a.user_name like ? ");
                    params.add("%" + mapSelect.get("value") + "%");
                }
                break;
                //联系人电话
                case "4": {
                    sql.append(" and a.user_phone like ? ");
                    params.add("%" + mapSelect.get("value") + "%");
                }
                break;
            }
        }
        //筛选输入项
        String storeName = String.valueOf(data.get("storeName"));
        if(!"null".equals(storeName.toLowerCase()) && storeName.length()>0){
            sql.append(" and a.store_name like ? ");
            params.add("%" + storeName + "%");
        }
        String storeArea = String.valueOf(data.get("storeArea"));
        if(!"null".equals(storeArea.toLowerCase()) && storeArea.length()>0){
            sql.append(" and a.store_area like ? ");
            params.add("%" + storeArea + "%");
        }
        String storeChanel = String.valueOf(data.get("storeChanel") );
        if(!"null".equals(storeChanel.toLowerCase()) && storeChanel.length()>0){
            sql.append(" and a.store_chanel like ? ");
            params.add("%" + storeChanel + "%");
        }

        sql.append(" order by a.compulsory_start_date desc,a.contractstartinsurance desc ");
//        params.add(TokenUtil.getCompanyId(uid));

        return this.iTCrmCarInsuranceDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * @param start       当前页面
     * @param limit       页面大小
     * @param searchValue 车牌号或者VIN
     * @return Map
     * @throws Exception
     * @desc
     * @author 术洪超
     */

    @Override
    public Map pageOwner(String uid, String start, String limit, String searchValue) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from T_CRM_CAR c where  ");
        List params = new ArrayList();
        String oId = TokenUtil.getCompanyId(uid);
        sql.append("and c.company_id = ? ");
        params.add(oId);

        if (!StringUtils.isEmpty(searchValue)) {
            sql.append("and (c.owner_name like ? or c.plate_num like ?)");
            params.add("%" + searchValue + "%");
            params.add("%" + searchValue + "%");
        }
//        Constant.VIN_TO_SQL(sql,uid,"c");
        confService.crmCarConfToSql(uid, sql, "c", oId);
//        // 车辆分配
//        // 查询其分配的车辆
//        Map info = refCar(uid);
//        //未分配车辆
//        if (null == info || info.size() == 0) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//
//        //车辆分配类型
//        String refType = String.valueOf(info.get("type"));
//        if (StringUtils.isEmpty(refType) || "null".equals(refType.toLowerCase())) {
//            throw new Exception("该用户未分配车辆，请联系管理员！");
//        }
//        //车辆分配值，车属号 或车尾数，vin时 另行处理
//        String refValue = String.valueOf(info.get("value"));
//        refValue = refValue.replaceAll(",", "");
//        //根据类型取车辆
//
//        switch (refType) {
//            case Constant.CAR_WH:
//                sql.append(" AND  plate_num REGEXP ? ");
//                params.add("[" + refValue + "]$");
//                break;
//            case Constant.CAR_WS:
//                //先不写
//                sql.append(" AND plate_num REGEXP ? ");
//                params.add(".*[" + refValue + "][A-Za-z]*$");
//                //根据尾数查询
//                break;
//            case Constant.CAR_ALL:
//                //全部车辆
//                break;
//            default:
//                throw new Exception("车辆分配错误，请联系管理员！");
//        }
        return this.iTCrmCarInsuranceDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * @param data TCrmCarInsurance实体
     * @return Map
     * @throws Exception
     * @desc
     * @author 术洪超
     */
    @Override
    public TCrmCarInsurance save(TCrmCarInsurance data) throws Exception {


        List params = new ArrayList();

        String sql = "select * from T_CRM_CAR_INSURANCE where 1=1 and vin = ? ";
        params.add(data.getVin());

        if (data.getId() != null) {
            sql += " and id != ? ";
            params.add(data.getId());
        }
        sql += " order by compulsory_end_date desc limit 1";

        Map map = this.iTCrmCarInsuranceDao.info(sql, params.toArray());

        //存在历史记录，判断合同开始-合同结束
        //存在历史记录，去执行判断
        if (null != map && map.size() > 0) {
//            String startDate = String.valueOf(map.get("compulsoryStartDate"));
//            String comEndDate = String.valueOf(map.get("compulsoryEndDate"));
            //如果合同没有结束日期，用开始日期+1年做为结束日期
            Long startDate = Double.valueOf(String.valueOf(map.get("compulsoryStartDate"))).longValue();

            Long stopDate;
            if (String.valueOf(map.get("complusoryEndDate")).equals("null")) {

                Calendar calendar = Calendar.getInstance();
                calendar.setTime(new Date(startDate));
                calendar.add(Calendar.YEAR, 1);
                stopDate = calendar.getTime().getTime();
                //System.out.println(stopDate);
            } else {
                stopDate = Double.valueOf(String.valueOf(map.get("compulsoryEndDate"))).longValue();
            }

            if (Double.valueOf(data.getContractstartinsurance()).longValue() < stopDate || data.getContractstartinsurance() < data.getContractstopinsurance()) {
                throw new Exception("已存在相同里程区间或合同起止区间的记录，请确认后再提交！");
            }

        }

        return (TCrmCarInsurance) this.iTCrmCarInsuranceDao.saveOrUpdate(data);
    }

    /**
     * @param start
     * @param limit
     * @return Map
     * @throws Exception
     * @desc
     * @author 徐钦勇
     */
    @Override
    public Map impPage(String start, String limit, String oid) throws Exception {
        String sqlBuffer = "select * from " + tabName + " where 1=1 ";
        List<String> paramList = new ArrayList<String>();
        if (!StringUtils.isEmpty(oid)) {
            sqlBuffer += " and company_id = ? ";
            paramList.add(oid);
        }
        return this.iImpJdbcDao.queryForPageMap(start, limit, sqlBuffer, paramList.toArray());

    }

    /**
     * 根据 实体 ID，删除记录
     *
     * @param objId
     * @return
     */
    @Override
    public boolean clearById(String objId) throws Exception {
        String hql = "delete from TCrmCarInsurance where id = :id";
        Map params = new HashMap();
        params.put("id", objId);
        this.iImpHibernateDao.executeHqlWithParams(hql, params);
        return true;
    }

    /**
     * @param id 核销 id
     * @return Map
     * @throws Exception
     * @desc 导入确认
     * @author 徐钦勇
     */
    @Override
    public void impConfirm(String id, String uid) throws Exception {
        String EXIST_DATA_SQL = "select * from " + tabName + " where  company_id = ? ";
        TCrmCarInsurance insurance;
        Map existData;
        List<Object> idList = new ArrayList();
        Object obj = this.iImpHibernateDao.findEntity(TCrmCarInsurance.class, id);
        if (obj == null) {
            throw new Exception("无效的数据！");
        }
        //源数据主键
        idList.add(id);
        insurance = (TCrmCarInsurance) obj;
        String syx = insurance.getCommercialinsurancenum();
        String jqx = insurance.getCompulsoryinsurancenum();
        // 商业险 或 交强险 二选一，或者都选
        if (syx == null && jqx == null) {
            throw new Exception("无效的保单数据！");
        }
        if (!jqx.isEmpty()) {
            EXIST_DATA_SQL += " and compulsoryinsurancenum = ? limit 1 ";
            existData = this.jdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, insurance.getCompanyId(), jqx);

        } else {
            EXIST_DATA_SQL += " and commercialinsurancenum = ? limit 1 ";
            existData = this.jdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, insurance.getCompanyId(), syx);
        }
        if (existData.size() == 0) {
            insurance.setId(null);
            insurance.setConfirmDate(System.currentTimeMillis());
            insurance.setConfirmer(uid);
            insurance.setCreateTime(System.currentTimeMillis());
            insurance.setUpdateTime(System.currentTimeMillis());
        } else {
            //已有数据
            insurance.setId(existData.get("id").toString());
            insurance.setUpdateTime(System.currentTimeMillis());
            insurance.setConfirmer(uid);
            insurance.setConfirmDate(System.currentTimeMillis());
        }
        this.iTCrmCarInsuranceDao.saveOrUpdate(insurance);
        this.iImpJdbcDao.batchDel(tabName, "id", idList);
        idList.clear();
    }

    /**
     * @param data 批量数据
     * @return Map
     * @throws Exception
     * @desc 续保批量确认
     * @author 徐钦勇
     */
    @Override
    public void multiImpConfirm(List<Map> data, String uid) throws Exception {
        if (data.size() == 0) {
            throw new Exception("请选择需要确认数据");
        }
        TCrmCarInsurance insurance;
        Map existData;
        List<Object> idList = new ArrayList();
        for (Map map : data) {
            String EXIST_DATA_SQL = "select * from " + tabName + " where  company_id = ? ";
            String tempId = String.valueOf(map.get("id"));
            Object obj = iImpHibernateDao.findEntity(TCrmCarInsurance.class, tempId);
            if (obj == null) {
                throw new Exception("无对应数据！");
            }

            insurance = (TCrmCarInsurance) obj;
            String syx = insurance.getCommercialinsurancenum();
            String jqx = insurance.getCompulsoryinsurancenum();
            // 商业险 或 交强险 二选一，或者都选
            if (syx == null && jqx == null) {
                continue;
            }
            //源数据主键
            idList.add(tempId);
            if (jqx != null) {
                EXIST_DATA_SQL += " and compulsoryinsurancenum = ? limit 1";
                existData = this.jdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, insurance.getCompanyId(), jqx);

            } else {
                EXIST_DATA_SQL += " and commercialinsurancenum = ? limit 1";
                existData = this.jdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, insurance.getCompanyId(), syx);
            }
            if (existData.size() == 0) {
                //通过车牌号或 Vin 查询车辆
                insurance.setId(null);
                insurance.setConfirmDate(System.currentTimeMillis());
                insurance.setConfirmer(uid);
                insurance.setCreateTime(System.currentTimeMillis());
                insurance.setUpdateTime(System.currentTimeMillis());
            } else {
                //已有数据
                insurance.setId(existData.get("id").toString());
                insurance.setUpdateTime(System.currentTimeMillis());
                insurance.setConfirmer(uid);
                insurance.setConfirmDate(System.currentTimeMillis());
            }
            this.iTCrmCarInsuranceDao.saveOrUpdate(insurance);
        }
        this.iImpJdbcDao.batchDel(tabName, "id", idList);
        idList = null;
    }

    /**
     * @return Map
     * @throws Exception
     * @desc 续保全部确认
     * @author 徐钦勇
     */
    @Override
    public void impConfirmAll(String uid) throws Exception {
        //获取公司 ID
        String companyId = TokenUtil.getCompanyId(uid);
        //  //目标数据库
        String targetSchema = iHibernateDao.Schema();
        //来源数据库
        String sourceSchema = iImpHibernateDao.Schema();
        String targeTable = targetSchema + ".t_crm_car_insurance";
        String sourceTable = sourceSchema + ".t_crm_car_insurance";

        if (StringUtils.isEmpty(targetSchema) || StringUtils.isEmpty(sourceSchema)) {
            throw new Exception("未获取到数据库实例请联系管理员");
        }

        /******
         * 保险台账为应对无保单的情况，由原来保单号验证，特改为由车架号、起保日期或车牌号起保日期验证
         * 既无车架号又无车牌号的数据，无保单号、无起保日期的数据忽略
         * 有VIN或车牌号，无起保日期的数据忽略，交强或商业险有一个起保日期即可
         *
         * *****/

        /*****
         * 1、商业险为主同步
         * 2、交强险为主同步
         * 3、增圾数据清除
         *
         * ***/

        /***
         * 更新SQL
         * **/
        StringBuffer syUpdate = new StringBuffer();
        syUpdate.append("    update " + targeTable + "  c , " + sourceTable + " imp set ");
        syUpdate.append("     c.age_start_date= imp.age_start_date,");
        syUpdate.append("     c.base_type=imp.base_type,");
        syUpdate.append("     c.boli=imp.boli,");
        syUpdate.append("     c.boli_end_date=imp.boli_end_date,");
        syUpdate.append("     c.boli_start_date=imp.boli_start_date,");
        syUpdate.append("     c.car_series=imp.car_series,");
        syUpdate.append("     c.commercial_company=imp.commercial_company,");
        syUpdate.append("     c.commercialinsurancenum=imp.commercialinsurancenum,");
        syUpdate.append("     c.company_id=imp.company_id,");
        syUpdate.append("     c.compulsory_company=imp.compulsory_company,");
        syUpdate.append("     c.compulsory_end_date=imp.compulsory_end_date,");
        syUpdate.append("     c.compulsoryinsurancenum=imp.compulsoryinsurancenum,");
        syUpdate.append("     c.compulsory_start_date=imp.compulsory_start_date,");
        syUpdate.append("     c.contractstartinsurance=imp.contractstartinsurance,");
        syUpdate.append("     c.contractstopinsurance=imp.contractstopinsurance,");
        syUpdate.append("     c.huahen=imp.huahen,");
        syUpdate.append("     c.huahen_end_date=imp.huahen_end_date,");
        syUpdate.append("     c.huahen_start_date=imp.huahen_start_date,");
        syUpdate.append("     c.iscompulsoryinsurance=imp.iscompulsoryinsurance,");
        syUpdate.append("     c.plate_num=imp.plate_num,");
        syUpdate.append("     c.type=imp.type,");
        syUpdate.append("     c.user_name=imp.user_name,");
        syUpdate.append("     c.user_tel=imp.user_tel,");
        syUpdate.append("     c.store_name=imp.store_name,");
        syUpdate.append("     c.store_area=imp.store_area,");
        syUpdate.append("     c.store_chanel=imp.store_chanel,");
        syUpdate.append("     c.sale=imp.sale,");
        syUpdate.append("     c.licence_date=imp.licence_date,");
        syUpdate.append("     c.store_p_area=imp.store_p_area,");
        syUpdate.append("     c.vin=imp.vin");
        syUpdate.append(" 		");
        syUpdate.append(" 		where ");
        syUpdate.append(" 		c.company_id= '" + companyId + "'");
        syUpdate.append(" 		and	c.company_id = imp.company_id ");

        /**商业险更新SQL**/
        StringBuffer syxUpdateSQL = new StringBuffer(syUpdate.toString());
        syxUpdateSQL.append("  and c.contractstartinsurance = imp.contractstartinsurance  ");

        /**交强险更新SQL**/
        StringBuffer jqxUpdateSQL = new StringBuffer(syUpdate.toString());
        jqxUpdateSQL.append(" and c.compulsory_start_date = imp.compulsory_start_date  ");

        /*****新保通过车架号***/
        String newSyxSQL = syxUpdateSQL.toString() + " and  c.vin = imp.vin ";
        String newJqxSQL = jqxUpdateSQL.toString() + " and  c.vin = imp.vin ";

        /*****续保通过车牌号*****/
        String xbSyxSQL = syxUpdateSQL.toString() + " and c.plate_num = imp.plate_num ";
        String xbJqxSQL = jqxUpdateSQL.toString() + " and c.plate_num = imp.plate_num ";


        StringBuffer delSQL = new StringBuffer();
        delSQL.append("delete from " + sourceTable + " USING " + targeTable + "," + sourceTable + " WHERE " + targeTable + ".company_id = " + sourceTable + ".company_id  and " + targeTable + ".company_id ='" + companyId + "' ");

        //新保删除
        StringBuffer newDel = new StringBuffer();
        newDel.append(delSQL.toString()).append(" and  " + sourceTable + ".vin = " + targeTable + ".vin   ");
        //续保删除
        StringBuffer xbDel = new StringBuffer();
        xbDel.append(delSQL.toString()).append(" and " + sourceTable + ".plate_num = " + targeTable + ".plate_num");

        /***删除新保商业险SQL**/
        StringBuffer newsSyDelSQL = new StringBuffer(newDel.toString());
        newsSyDelSQL.append(" and  " + sourceTable + ".contractstartinsurance = " + targeTable + ".contractstartinsurance");

        /***删除新保交强险SQL***/
        StringBuffer newJqDelSQL = new StringBuffer(newDel.toString());
        newJqDelSQL.append(" and  " + sourceTable + ".compulsory_start_date = " + targeTable + ".compulsory_start_date");

        /***删除续保商业险SQL**/
        StringBuffer xbSyDelSQL = new StringBuffer(xbDel.toString());
        xbSyDelSQL.append(" and  " + sourceTable + ".contractstartinsurance = " + targeTable + ".contractstartinsurance");

        /***删除续保交强险SQL***/
        StringBuffer xbJqDelSQL = new StringBuffer(xbDel.toString());
        xbJqDelSQL.append(" and  " + sourceTable + ".compulsory_start_date = " + targeTable + ".compulsory_start_date");


        StringBuffer selectField = new StringBuffer();
        selectField.append("    id, ");
        selectField.append("    age_start_date,   ");
        selectField.append("    base_type, ");
        selectField.append("    boli, ");
        selectField.append("    boli_end_date, ");
        selectField.append("    boli_start_date, ");
        selectField.append("    car_series, ");
        selectField.append("    commercial_company, ");
        selectField.append("    commercialinsurancenum, ");
        selectField.append("    company_id, ");
        selectField.append("    compulsory_company, ");
        selectField.append("    compulsory_end_date, ");
        selectField.append("    compulsoryinsurancenum, ");
        selectField.append("    compulsory_start_date, ");
        selectField.append("    confirm_date, ");
        selectField.append("    confirmer, ");
        selectField.append("    contractstartinsurance, ");
        selectField.append("    contractstopinsurance, ");
        selectField.append("    huahen, ");
        selectField.append("    huahen_end_date, ");
        selectField.append("    huahen_start_date, ");
        selectField.append("    iscompulsoryinsurance, ");
        selectField.append("    plate_num, ");
        selectField.append("    type, ");
        selectField.append("    user_carid, ");
        selectField.append("    user_name, ");
        selectField.append("    user_tel, ");
        selectField.append("    store_area, ");
        selectField.append("    store_name, ");
        selectField.append("    store_chanel, ");
        selectField.append("    store_p_area, ");
        selectField.append("    licence_date, ");
        selectField.append("    sale, ");
        selectField.append("    vin ");

        /***插入SQL****/
        StringBuffer insertSQL = new StringBuffer();
        insertSQL.append("insert into " + targeTable + "(" + selectField.toString() + ") select " + selectField.toString() + " from " + sourceTable + " where company_id='" + companyId + "' ");
        /*****/

        //更新新保-商业险
        int upRowCount = jdbcDao.update(newSyxSQL.toString());
        System.out.println("-------------------更新新保商业险数---------" + upRowCount);

        //更新新保-新保交强险
        upRowCount = jdbcDao.update(newJqxSQL.toString());
        System.out.println("-------------------更新新保交强险数---------" + upRowCount);

        // 删除新保
        //更新新保-交强险
        int delRowCount = iImpJdbcDao.update(newJqDelSQL.toString());
        delRowCount += iImpJdbcDao.update(newsSyDelSQL.toString());
        System.out.println("-------------------删除新保保单数---------" + delRowCount);

        //更新续保-商业
        upRowCount = jdbcDao.update(xbSyxSQL.toString());
        System.out.println("-------------------更新商业保单数---------" + upRowCount);
        //更新续保-交强
        upRowCount = jdbcDao.update(xbJqxSQL.toString());
        System.out.println("-------------------更新商业保单数---------" + upRowCount);

        //删除续保
        delRowCount = iImpJdbcDao.update(xbSyDelSQL.toString());
        delRowCount += iImpJdbcDao.update(xbJqDelSQL.toString());
        System.out.println("-------------------删除商业保单数---------" + delRowCount);

        //新增记录数
        int addRowCount = jdbcDao.update(insertSQL.toString());
        System.out.println("-------------------新增保单数---------" + addRowCount);

        //删除新增记录数
        int delAddRowCount = iImpJdbcDao.update(newDel.toString());
        System.out.println("-------------------删除新保单数工单数---------" + delAddRowCount);
        delAddRowCount = iImpJdbcDao.update(xbDel.toString());
        System.out.println("-------------------删除续保单数工单数---------" + delAddRowCount);

//        //编写查询源数据 HQL
//        String hql = "from TCrmCarInsurance where companyId = :companyId";
//        //设置参数
//        Map<String, Object> params = new HashMap();
//        params.put("companyId", companyId);
//        //查询公司下的全部续保
//        List<TCrmCarInsurance> tCrmCarInsurance = iImpHibernateDao.findEntityListByHqlWithParams(hql, params);
//        if (tCrmCarInsurance == null) {
//            throw new Exception("无对应数据！");
//        }
//        //每50条保存一次
//        List<Object> idList = new ArrayList<>();
//        Map existData;
//        String tempId;
//        String EXIST_DATA_SQL;
//        for (TCrmCarInsurance insurance : tCrmCarInsurance) {
//            EXIST_DATA_SQL = "select * from " + tabName + " where  company_id = ? ";
//
//            String syx = insurance.getCommercialinsurancenum();
//            String jqx = insurance.getCompulsoryinsurancenum();
//            // 商业险 或 交强险 二选一，或者都选
//            if (syx == null && jqx == null) {
//                continue;
//            }
//            //源数据主键
//            tempId = insurance.getId();
//            idList.add(tempId);
//            if (jqx != null) {
//                EXIST_DATA_SQL += " and compulsoryinsurancenum = ? limit 1";
//                existData = this.jdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, insurance.getCompanyId(), jqx);
//            } else {
//                EXIST_DATA_SQL += " and commercialinsurancenum = ? limit 1";
//                existData = this.jdbcDao.queryForMapBySqlWithParams(EXIST_DATA_SQL, insurance.getCompanyId(), syx);
//            }
//            if (existData.size() == 0) {
//                insurance.setId(null);
//                insurance.setConfirmDate(System.currentTimeMillis());
//                insurance.setConfirmer(uid);
//                insurance.setCreateTime(System.currentTimeMillis());
//                insurance.setUpdateTime(System.currentTimeMillis());
//
//            } else {
//                //已有数据
//                insurance.setId(existData.get("id").toString());
//                insurance.setUpdateTime(System.currentTimeMillis());
//                insurance.setConfirmer(uid);
//                insurance.setConfirmDate(System.currentTimeMillis());
//            }
//            //保存或更新
//            this.iHibernateDao.saveOrUpdateEntity(insurance);
//            if (idList.size() == 500) {
//                this.iImpJdbcDao.batchDel(tabName, "id", idList);
//                idList.clear();
//            }
//        }
//        //处理剩余数据
//        if (idList.size() > 0) {
//            this.iImpJdbcDao.batchDel(tabName, "id", idList);
//            idList.clear();
//        }
        //y同步车辆id
        // this.itCrmCarWorkorderTaskService.updWorkOrderCaridInfoQuartz();
        //同步车型车系
        // this.iCarBaseCountService.updateExCarSeriesQuartz();
        //同步结束时间
        this.itCrmTimeTaskService.updInsuranceEndDateQuartz();
        //更新三连保
//        this.updCrmCarInsuranctContinuation(3, companyId);
//        //更新二连保
//        this.updCrmCarInsuranctContinuation(2, companyId);
        this.addToTcrmCarInfos(companyId);
    }

    private void addToTcrmCarInfos(String companyId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append(" insert into t_crm_car(id, create_time, create_user,company_id,contact_name, contact_phone,series , " +
                " contact_tel,guarantee_start_date,  owner_name, owner_phone,owner_tel, plate_num,vin," +
                " store_name,store_p_area,store_area,store_area_code,store_p_area_code,store_code)  " +
                " select car.id,car.create_time,car.create_user,car.company_id,car.user_name,car.user_tel,car.car_series,  " +
                " car.user_tel,car.licence_date,car.user_name,car.user_tel,car.user_tel,car.plate_num,car.vin," +
                " car.store_name,car.store_p_area,car.store_area,car.store_area_code,car.store_p_area_code,car.store_code  " +
                " from t_crm_car_insurance car where car.company_id=? and car.plate_num=car.vin " +
                " and not EXISTS (select * from t_crm_car where plate_num =car.plate_num) ");
        this.iTCrmCarInsuranceDao.executeSQL(sql.toString(),companyId);

        StringBuffer sqlVin=new StringBuffer();
        sqlVin.append(" insert into t_crm_car(id, create_time, create_user,company_id,contact_name, contact_phone,series, " +
                " contact_tel,guarantee_start_date,  owner_name, owner_phone,owner_tel, plate_num,vin," +
                " store_name,store_p_area,store_area,store_area_code,store_p_area_code,store_code)  " +
                " select car.id,car.create_time,car.create_user,car.company_id,car.user_name,car.user_tel,car.car_series,  " +
                " car.user_tel,car.licence_date,car.user_name,car.user_tel,car.user_tel,car.plate_num,car.vin," +
                " car.store_name,car.store_p_area,car.store_area,car.store_area_code,car.store_p_area_code,car.store_code  " +
                " from t_crm_car_insurance car where car.company_id=? and car.plate_num<>car.vin " +
                " and not EXISTS (select * from t_crm_car where plate_num =car.plate_num) ");
        this.iTCrmCarInsuranceDao.executeSQL(sqlVin.toString(),companyId);
    }

    /**
     * 更新二连保 三连保
     * @param continuation
     * @param companyId
     * @throws Exception
     */
    private void updCrmCarInsuranctContinuation(int continuation, String companyId) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE t_crm_car_insurance ins," +
                " (select * from (" +
                " SELECT * FROM(SELECT FROM_UNIXTIME(contractstartinsurance / 1000,'%Y') insur_year," +
                " vin,commercialinsurancenum,company_id,plate_num" +
                " FROM" +
                " t_crm_car_insurance" +
                " WHERE company_id = ? AND contractstartinsurance IS NOT NULL AND continuation is null" +
                " AND FROM_UNIXTIME(contractstartinsurance / 1000,'%Y') IN ('2016','2017','2018','2019')" +
                " ) temp GROUP BY vin, insur_year" +
                " ) temp2 GROUP BY temp2.vin ");
        if (continuation == 3) {
            sql.append(" HAVING COUNT(temp2.vin) >= 3");
        }
        if (continuation == 2) {
            sql.append(" HAVING COUNT(temp2.vin) = 2");
        }
        sql.append(" ) temp3" +
                " SET ins.continuation = " + continuation +
                " WHERE" +
                " ins.company_id = temp3.company_id" +
                " AND ins.vin = temp3.vin");
        this.iTCrmCarInsuranceDao.executeSQL(sql.toString(), companyId);
    }


    private Map<String, Object> refCar(String uid) throws Exception {
        String sql = "select  * from t_crm_car_ref where u_id = ? and status = '1' ";
        Map info = this.iTCrmCarInsuranceDao.info(sql, uid);
        return info;
    }


    /**
     * @return
     * @throws Exception
     * @desc 删除全部待导入数据
     * @author 术洪超
     */
    @Override
    public void impDelAll(String uid) throws Exception {

        Map map = new HashMap();
        String companyId = TokenUtil.getCompanyId(uid);
        String hql = " delete from TCrmCarInsurance where companyId =:companyId";
        map.put("companyId", companyId);
        //System.out.println(companyId);

        this.iImpHibernateDao.executeHqlWithParams(hql, map);
    }

    @Override
    public void impDels(List<Map<Object, String>> data, String uid) throws Exception {
        List<Object> idList = new ArrayList();
        for (Map map : data) {
            idList.add(map.get("id"));
        }
        this.iImpJdbcDao.batchDel(tabName, "id", idList);
    }

}
